----------------------------------------------
-- Session 3 - XML
----------------------------------------------

----------------------------------------------
-- XML Data Type

SET NOCOUNT ON
CREATE TABLE Candidate (ID int, Name varchar(50), Resume xml)

-- 

INSERT Candidate VALUES (1,'Smith','<resume>none</resume>')
SELECT * FROM Candidate

-- 

INSERT Candidate VALUES (1,'Smith','none')
INSERT Candidate VALUES (1,'Smith','<resume>none</resume><resume>none</resume>')
SELECT * FROM Candidate

-- 

INSERT Candidate VALUES (1,'Smith','<resume>none</Resume>')
INSERT Candidate VALUES (1,'Smith','no resume & no CV')
 
-- 

TRUNCATE TABLE Candidate
ALTER TABLE Candidate
 ADD CONSTRAINT chkResume CHECK (Resume.exist('//education') = 1)

-- 

INSERT Candidate VALUES (1,'Smith','<resume>none</resume>')

--

INSERT Candidate VALUES (1,'Smith','<resume><education>Harvard</education></resume>')

----------------------------------------------
-- XML Schema

CREATE XML SCHEMA COLLECTION ResumeSchema AS N''

-- Schema

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
		xmlns="resume-schema"
		targetNamespace="resume-schema"
		elementFormDefault="qualified">

<xsd:element name="Resume" type="ResumeType"/>
<xsd:complexType name="ResumeType" mixed="true">
 <xsd:sequence>
  <xsd:element name="Name" type="NameType" minOccurs="0"/>
  <xsd:element name="Objective" type="xsd:string" minOccurs="0"/>
  <xsd:element name="Experience" type="Experience" minOccurs="0" maxOccurs="unbounded"/>
  <xsd:element name="Education" type="Education" minOccurs="0" maxOccurs="unbounded"/>
  <xsd:element name="InterestSection" type="xsd:string" minOccurs="0"/>
 </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Experience" mixed="true">
  <xsd:sequence>
    <xsd:element name="YearFrom" type="xsd:int"/>
    <xsd:element name="YearTo" type="xsd:int"/>
    <xsd:element name="Title" type="xsd:string"/>
    <xsd:element name="Description" type="xsd:string"/>
  </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Education" mixed="true">
  <xsd:sequence>
    <xsd:element name="YearFrom" type="xsd:int"/>
    <xsd:element name="YearTo" type="xsd:int"/>
    <xsd:element name="Description" type="xsd:string"/>
  </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="NameType" mixed="true">
 <xsd:all>
  <xsd:element name="FirstName" type="xsd:string"/>
  <xsd:element name="LastName" type="xsd:string"/>
 </xsd:all>
</xsd:complexType>
</xsd:schema>

--

SELECT * FROM sys.xml_schema_collections

-- Create Table

DROP TABLE Candidate
CREATE TABLE Candidate (ID int, Name varchar(50), Resume xml (ResumeSchema))

--

INSERT Candidate VALUES (1,'Smith','')

-- Smith.xml

<?xml version="1.0" encoding="utf-8" ?>
<Resume xmlns="resume-schema">
<Name>
<FirstName>Pat</FirstName>
<LastName>Smith</LastName>
</Name>
<Objective>Speak about Yukon and Whidbey</Objective>
<Experience>
<YearFrom>1993</YearFrom>
<YearTo>1999</YearTo>
<Title>Software Developer</Title>
<Description>Built lots of really cool software!</Description>
</Experience>
<Experience>
<YearFrom>2000</YearFrom>
<YearTo>2005</YearTo>
<Title>Trainer and Author</Title>
<Description>Wrote books and spoke at lots of conferences</Description>
</Experience>
<Education>
<YearFrom>1989</YearFrom>
<YearTo>1993</YearTo>
<Description>Podunk University</Description>
</Education>
<InterestSection>I like pudding!</InterestSection>
</Resume>

----------------------------------------------
-- FOR XML Improvements

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML AUTO

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML AUTO, ELEMENTS

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  ContactID < 10
FOR XML PATH

--

SELECT Title AS '@Title',
       FirstName as 'Name/First',
       LastName as 'Name/Last',
       EmailAddress as 'Email'
FROM   Person.Contact 
WHERE  ContactID < 10 
FOR XML PATH ('Person')

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, ELEMENTS

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, ELEMENTS XSINIL

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, XMLDATA

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, XMLSCHEMA

--

SELECT Title, FirstName, LastName, EmailAddress
FROM   Person.Contact
WHERE  FirstName = 'Cathan'
FOR XML AUTO, ELEMENTS, XMLSCHEMA

--

DECLARE @XML XML
SET @XML = 
(SELECT Title, FirstName, LastName, EmailAddress FROM Person.Contact
   WHERE ContactID < 10 FOR XML PATH)
SELECT @XML

----------------------------------------------
-- XQUERY

DECLARE @xmldoc xml
SET @xmldoc = '<people>
 <person>
  <name>
   <givenName>Martin</givenName>
   <familyName>Gudgin</familyName>
  </name>
  <age>33</age>
  <height>short</height>
 </person>
 <person>
  <name>
   <givenName>Simon</givenName>
   <familyName>Horrell</familyName>
  </name>
  <age>40</age>
  <height>short</height>
 </person>
 <person>
  <name>
   <givenName>Mark</givenName>
   <familyName>Szolkowski</familyName>
  </name>
  <age>30</age>
  <height>medium</height>
 </person>
</people>'

SELECT @xmldoc

-- Return names

SELECT @xmldoc.query(' 
(: SQL Server 2005 :)
(: doc function not used :)
for $p in /people/person
return $p/name') 

-- Return people older than 30 (XPATH way)

SELECT @xmldoc.query(' 
(: this uses an XPath predicate :)
/people/person[age > 30]')

-- Return people older than 30 (FLWOR way)

SELECT @xmldoc.query(' 
(: this uses a where  :)
for $p in /people/person
where $p/age > 30
order by $p/age[1]
return $p/name')

-- Getting Creative

SELECT @xmldoc.query(' 
(: this uses a where  :)
for $p in /people/person
where $p/age > 30
order by $p/age[1]
return <foo><bar>{$p/name}</bar></foo>')

----------------------------------------------
-- XML DML

UPDATE HumanResources.JobCandidate SET Resume = ' entire new xml document '

--

DECLARE @myDoc xml       
SET @myDoc = 
 '<Student ID="1" Name="Bill Gates"><Experience></Experience></Student>'
SELECT @myDoc as Example

--

SET @myDoc.modify('insert <Development>Built Microsoft BOB</Development>
 into (/Student/Experience)[1]') 
SELECT @myDoc

--

SET @myDoc.modify('
 replace value of (/Student/Experience/Development[1]/text())[1]
 with "Built Donkey .NET"')
SELECT @myDoc

--

SET @myDoc.modify('delete /Student/Experience/Development[1]')
SELECT @myDoc
